﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
/// <summary>
/// Summary description for datacessDB
/// </summary>
public class datacessDB
{
	public datacessDB()
	{
	}
    private string conn = ConfigurationManager.ConnectionStrings["eproject_sem3"].ConnectionString.ToString();
    public List<Customer> getAllCustomer()
    {
        List<Customer> lstCustomer = new List<Customer>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from customer";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Customer c = new Customer();
            c.CustomerId = reader.GetInt32(0);
            c.Username = reader.GetString(1);
            c.Password = reader.GetString(2);
            c.Name = reader.GetString(3);
            c.Birthday = reader.GetDateTime(4);
            c.Gender = reader.GetString(5);
            c.Email = reader.GetString(6);
            c.Status = reader.GetString(7);
            lstCustomer.Add(c);
        }
        con.Close();
        return lstCustomer;
    }
    public List<Admins> getAllAdmin()
    {
        List<Admins> lstAdmin = new List<Admins>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from Admins";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Admins ad = new Admins();
            ad.AdminId = reader.GetInt32(0);
            ad.Username = reader.GetString(1);
            ad.Password = reader.GetString(2);
            lstAdmin.Add(ad);
        }
        con.Close();
        return lstAdmin;
    }

    public List<Ecard> getAllEcard()
    {
        List<Ecard> lstEcard = new List<Ecard>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from Ecard";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Ecard e = new Ecard();
            e.EcardId = reader.GetInt32(0);
            e.UrlImage = reader.GetString(1);
            e.UrlFlash = reader.GetString(2);
            e.EcardName = reader.GetString(3);
            e.AddDate = reader.GetDateTime(4);
            e.TypeId = reader.GetInt32(5);
            e.Count = reader.GetInt32(6);
            lstEcard.Add(e);
        }
        con.Close();
        return lstEcard;
    }
    public List<SentEcard> getAllSentEcard()
    {
        List<SentEcard> lstSentEcard = new List<SentEcard>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from SentEcard";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            SentEcard se = new SentEcard();
            se.SentEcardId = reader.GetInt32(0);
            se.CustomerId = reader.GetInt32(1);
            se.EcardId = reader.GetInt32(2);
            se.DateSent = reader.GetDateTime(3);
            se.Title = reader.GetString(4);
            se.Massage = reader.GetString(5);
            se.Signature = reader.GetString(6);
            lstSentEcard.Add(se);
        }
        con.Close();
        return lstSentEcard;
    }
    public List<listEmail> getAllListEmail()
    {
        List<listEmail> lstListEmail = new List<listEmail>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from listEmail";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            listEmail email = new listEmail();
            email.SentEcardId = reader.GetInt32(0);
            email.Email = reader.GetString(1);
            email.Name = reader.GetString(2);
            lstListEmail.Add(email);
        }
        con.Close();
        return lstListEmail;
    }
    public List<LogSentMail> getLogSentMail()
    {
        List<LogSentMail> lstLogSentMail = new List<LogSentMail>();
        SqlConnection con = new SqlConnection(conn);
        con.Open();
        string query = "Select * from logSentMail";
        SqlCommand command = new SqlCommand(query, con);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            LogSentMail lsm = new LogSentMail();
            lsm.LogSentMailId = reader.GetInt32(0);
            lsm.CustomerId = reader.GetInt32(1);
            lsm.NameSender = reader.GetString(2);
            lsm.EmailSender = reader.GetString(3);
            lsm.EcardId = reader.GetInt32(4);
            lsm.DateSent = reader.GetDateTime(5);
            lsm.Title = reader.GetString(6);
            lsm.Messages = reader.GetString(7);
            lsm.Signatures = reader.GetString(8);
            lsm.EmailReceived = reader.GetString(9);
            lsm.NameReceived = reader.GetString(10);
            lstLogSentMail.Add(lsm);
        }
        con.Close();
        return lstLogSentMail ;
    }
    public int getSentEcardId(int customerId)
    {
        int id = 0;
        SqlConnection con = new SqlConnection(conn);
        string query = "select top 1 sentEcardId from sentEcard where customerId =" + customerId + " order by sentEcardId desc ";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            id = reader.GetInt32(0);
        }
        con.Close();
        return id;

    }
    public int getLogSentMailId(int customerId)
    {
        int id = 0;
        SqlConnection con = new SqlConnection(conn);
        string query = "select top 1 LogSentMailId from LogSentMail where customerId =" + customerId + " order by LogSentMailId desc ";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            id = reader.GetInt32(0);
        }
        con.Close();
        return id;

    }
    public Customer getCustomerById(int id)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from customer where customerId =" + id + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        Customer c = new Customer();
        while (reader.Read())
        {
            c.CustomerId = reader.GetInt32(0);
            c.Username = reader.GetString(1);
            c.Password = reader.GetString(2);
            c.Name = reader.GetString(3);
            c.Birthday = reader.GetDateTime(4);
            c.Gender = reader.GetString(5);
            c.Email = reader.GetString(6);
            c.Status = reader.GetString(7);
        }

        con.Close();
        return c;
    }
    public SentEcard getSentEcardById(int id)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from SentEcard where sentEcardId =" + id + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        SentEcard se = new SentEcard();
        while (reader.Read())
        {

            se.SentEcardId = reader.GetInt32(0);
            se.CustomerId = reader.GetInt32(1);
            se.EcardId = reader.GetInt32(2);
            se.DateSent = reader.GetDateTime(3);
            se.Title = reader.GetString(4);
            se.Massage = reader.GetString(5);
            se.Signature = reader.GetString(6);
        }

        con.Close();
        return se;
    }
    public LogSentMail getLogSentMailById(int id)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from LogSentMail where LogSentMailId =" + id + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        LogSentMail lsm = new LogSentMail();
        while (reader.Read())
        {
            lsm.LogSentMailId = reader.GetInt32(0);
            lsm.CustomerId = reader.GetInt32(1);
            lsm.NameSender = reader.GetString(2);
            lsm.EmailSender = reader.GetString(3);
            lsm.EcardId = reader.GetInt32(4);
            lsm.DateSent = reader.GetDateTime(5);
            lsm.Title = reader.GetString(6);
            lsm.Messages = reader.GetString(7);
            lsm.Signatures = reader.GetString(8);
            lsm.EmailReceived = reader.GetString(9);
            lsm.NameReceived = reader.GetString(10);
        }

        con.Close();
        return lsm;
    }
    public Ecard getEcardById(int id)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from Ecard where EcardId =" + id + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        Ecard e = new Ecard();
        while (reader.Read())
        {
            e.EcardId = reader.GetInt32(0);
            e.UrlImage = reader.GetString(1);
            e.UrlFlash = reader.GetString(2);
            e.EcardName = reader.GetString(3);
            e.AddDate = reader.GetDateTime(4);
            e.TypeId = reader.GetInt32(5);
            e.Count = reader.GetInt32(6);
        }

        con.Close();
        return e;
    }

    public List<TypeEcard> getAllTypeEcard()
    {
        List<TypeEcard> lst = new List<TypeEcard>();
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from typeEcard ";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
     
        while (reader.Read())
        {
            TypeEcard te = new TypeEcard();
            te.TypeEcardId = reader.GetInt32(0);
            te.TypeCardName = reader.GetString(1);
            lst.Add(te);
        }
        con.Close();
        return lst;
    }
    public DataTable getView_Statistics_count()
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from Statistics_count ";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    public DataTable getAllLogSentMail()
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from logSentMail ";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    public DataTable getview_feedback()
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from view_feedback ";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    public DataTable getEcardByTypeId(int typeId)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select top 8 * from Ecard where typeEcardId ="+typeId+" order by NEWID()";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    public int getCount_View_Statistics_count()
    {
        int count = 0;
        SqlConnection con = new SqlConnection(conn);
        string query = "select sum(sent) as total from Statistics_count";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            count = reader.GetInt32(0);
        }
        con.Close();
        return count;
    }
    public int getSoLanGuiCardToDay()
    {
        int count = 0;
        SqlConnection con = new SqlConnection(conn);
        string query = "select count(*) from sentEcard where DATEDIFF(dd,dateSent,GETDATE()) = 0";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            count = reader.GetInt32(0);
        }
        con.Close();
        return count;
    }
    public DataTable getViewDisplayEcard()
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select * from displayEcard ";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    public DataTable getEcardNew()
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select top 8 * from Ecard order by ecardId desc ";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    public DataTable getEcardMostSent()
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "select top 8 * from Ecard order by counts desc ";
        SqlDataAdapter adapter = new SqlDataAdapter(query, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds.Tables[0];
    }
    // insert du lieu      -------------------------------------------------
    
    public void insertcustomer(Customer custom )
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into Customer values ('" + custom.Username + "','" + custom.Password + "','" + custom.Name + "','" + string.Format("{0:MM/dd/yyyy}", custom.Birthday) + "','" + custom.Gender + "','" + custom.Email + "','normal')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertSentEcard(SentEcard se)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into SentEcard values (" + se.CustomerId + "," + se.EcardId + ",'" + se.DateSent + "','" + se.Title + "','" +se.Massage + "','" + se.Signature + "')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertListEmail(listEmail le)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into listEmail values (" + le.SentEcardId+ ",'" + le.Email+ "','" + le.Name + "')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertLogSentMail(LogSentMail log)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into LogSentMail values ("+log.CustomerId+",'" + log.NameSender + "','" + log.EmailSender + "'," +log.EcardId + ",'"+log.DateSent+"','"+log.Title+"','"+log.Messages+"','"+log.Signatures+"','"+log.EmailReceived+"','"+log.NameReceived+"')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertTypeEcard(String nameTypeEcard)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into typeEcard values ('"+nameTypeEcard+"')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertEcard(Ecard ecard)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into Ecard values('" + ecard.UrlImage + "','" + ecard.UrlFlash + "','" + ecard.EcardName + "','" + string.Format("{0:MM/dd/yyyy}", DateTime.Now) + "'," + ecard.TypeId + ",0)";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void insertFeedback(int customerId,string content)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "insert into feedback values(" + customerId + ",'" + content + "','" + string.Format("{0:MM/dd/yyyy}", DateTime.Now) + "')";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    // update du lieu -------------------------------------------------

    public void updateCountEcard(int ecardId)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "update Ecard set counts = counts + 1 where ecardId = " + ecardId + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void updateTypeEcard(int TypeEcardId, string typeCardName)
    { 
        SqlConnection con = new SqlConnection(conn);
        string query = "update typeEcard set typeCardName = '" + typeCardName + "' where typeEcardId = "+TypeEcardId+"";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void updateEcard(int ecardId, string urlFlash ,string ecardName,int typeEcardId)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "update Ecard set urlFlash='" + urlFlash + "' ,ecardName='" + ecardName + "',typeEcardId=" + typeEcardId + " where ecardId ="+ecardId+"";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    // delete du lieu ---------------------------------------------
    public void deleteTyeEcard(int TypeEcardId)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "DELETE FROM typeEcard where typeEcardId ="+TypeEcardId+"";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void deleteEcard(int ecardId)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "DELETE FROM Ecard where ecardId =" + ecardId + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
    public void deleteFeedback(int feedbackId)
    {
        SqlConnection con = new SqlConnection(conn);
        string query = "DELETE FROM feedback where feedbackId =" + feedbackId + "";
        SqlCommand command = new SqlCommand(query, con);
        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }
}